const mysql = require('../db/mysql57');

const checkinDao = {
    /*
     * 专注模式打卡
     * 这样编写存在高并发问题 后续再修复
     */
    async focusCheckIn(uid, time, duration, now_time) {
        let result;
        let consecutiveDays;
        // 先检查昨天是否有打卡记录
        try {
            let sql = `select * from FoucsCheckin where uid = ? and DATE(time) = CURDATE() - INTERVAL 1 DAY`;
            let data = [uid];
            result = await mysql.sqlExec(sql, data);
            if (result.length == 0) {
                consecutiveDays = 1;
            } else {
                consecutiveDays = result[0].consecutiveDays + 1;
            }
        } catch (err) {
            return { success: false, msg: '操作数据库时出错，打卡失败', insertId: null };
        }
        // 再查询这次打卡记录的时间间隔是否合理，距离最近一次打卡的间隔
        try {
            let sql = `select DATE_FORMAT(time, '%Y-%m-%d %H:%i:%s') as time from FoucsCheckin where uid = ? order by time desc LIMIT 1`;
            let data = [uid];
            result = await mysql.sqlExec(sql, data);
            if (result.length) {
                let time = (new Date(result[0].time)).getTime();
                let now_time_ms = now_time.getTime();
                if (now_time_ms - time < ((duration * 60 * 1000) - 10000)) {
                    return { success: false, msg: '打卡时间间隔有误', insertId: null };
                }
            }
        } catch (err) {
            return { success: false, msg: '操作数据库时出错，打卡失败', insertId: null };
        }
        // 最后再插入一条新的记录
        let sql = `INSERT INTO FoucsCheckin (uid, time, focusTime, consecutiveDays) VALUES (?, ?, ?, ?)`;
        let data = [uid, time, duration, consecutiveDays];
        return mysql.sqlExec(sql, data);
    },
    // 获取今日专注时长
    getTodayfocusDuration(uid) {
        let sql = `select SUM(focusTime) as total_duration from FoucsCheckin where uid = ? and DATE(time) = CURDATE()`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    },
    // async checkinStatistics(uid) {
    //     // let sql = `SELECT
    //     //     (SELECT SUM(duration) FROM FoucsCheckin WHERE uid = ?) AS total_duration,
    //     //     (SELECT COUNT(DISTINCT DATE(time)) FROM FoucsCheckin WHERE uid = ?) AS total_days,
    //     //     0 AS consecutive_days`;
    //     // let data = [uid, uid];
    //     // return sqlExec(sql, data);
    //     let sql = `select (
    //             select SUM(duration) from FoucsCheckin where uid = ?
    //         ) as all_duration, (
    //             select COUNT(DISTINCT DATE(time)) from FoucsCheckin where uid = ? 
    //         ) as total_days, (
    //             select consecutiveDays from FoucsCheckin where uid = ? and id = (
    //                 select id from FoucsCheckin where time = (
    //                     SELECT MAX(time) FROM FoucsCheckin WHERE uid = ? LIMIT 1
    //                 ) LIMIT 1
    //             ) LIMIT 1
    //         ) as consecutiveDays`;
    //     let data = [ uid, uid, uid, uid ];
    //     return sqlExec(sql, data);
    // },
    // /*
    //  * 排行榜接口
    //  * 返回：总专注时长前100的名次
    //  * 这个接口后续应该接缓存才行，因为这个接口会频繁调用，并且还挺消耗数据库性能的
    //  * 缓存方案：
    //  *     1. 每次查询的时候，先从缓存中查询
    //  *       1.1 如果缓存中存在，并且时间不超过1分钟，就直接返回缓存中的数据
    //  *       1.2 如果缓存中存在，并且时间超过1分钟，就获得一把全局锁，然后查询数据库，然后把查询结果缓存起来，并且设置缓存过期时间，如果超过10分钟，就删除缓存
    //  *     2. 如果缓存中不存在，就获得一把全局锁，就查询数据库，然后把查询结果缓存起来，并且设置缓存过期时间，如果超过10分钟，就删除缓存
    //  */
    // async checkinRanking(uid) {
    //     let sql = `select
    //         fc.uid as uid,
    //         u.username as username,
    //         u.userIcon as userIcon,
    //         sum(fc.duration) as total_duration,
    //         COALESCE(
    //             (
    //                 SELECT f2.consecutiveDays
    //                 FROM FoucsCheckin AS f2
    //                 WHERE f2.uid = fc.uid AND DATE(f2.time) = CURDATE()
    //                 LIMIT 1
    //             ),
    //             (
    //                 SELECT f3.consecutiveDays
    //                 FROM FoucsCheckin AS f3
    //                 WHERE f3.uid = fc.uid AND DATE(f3.time) = CURDATE() - INTERVAL 1 DAY
    //                 LIMIT 1
    //             )
    //         ) as days
    //     from FoucsCheckin as fc
    //     left join User as u on u.id = fc.uid
    //     group by uid
    //     order by total_duration desc
    //     limit 100`;
    //     let data = [];
    //     return sqlExec(sql, data);
    // },
    // // 获取最近 7 天的专注时长
    // async checkinTrend(uid) {
    //     let sql = `SELECT
    //         DATE(time) AS checkin_date,
    //         SUM(duration) AS total_duration
    //     FROM FoucsCheckin
    //     WHERE uid = ? and time >= CURDATE() - INTERVAL 7 DAY
    //     GROUP BY DATE(time)
    //     ORDER BY checkin_date`;
    //     let data = [uid];
    //     return sqlExec(sql, data);
    // },
    // async getMainPageNeedInfo(uid) {
    //     let sql = `select
    //             (select SUM(duration) from FoucsCheckin where uid = ? and DATE(time) = CURDATE()) as today_duration,
    //             (COALESCE(
    //                 (
    //                     SELECT f2.consecutiveDays
    //                     FROM FoucsCheckin AS f2
    //                     WHERE f2.uid = ? AND DATE(f2.time) = CURDATE()
    //                     LIMIT 1
    //                 ),
    //                 (
    //                     SELECT f3.consecutiveDays
    //                     FROM FoucsCheckin AS f3
    //                     WHERE f3.uid = ? AND DATE(f3.time) = CURDATE() - INTERVAL 1 DAY
    //                     LIMIT 1
    //                 )
    //             )) as consecutiveDays,
    //             0 as points`;
    //     let data = [ uid, uid, uid ];
    //     return sqlExec(sql, data);
    // }

    /**
     * 每日打卡
     * 有可能出现高并发问题，后续再修复
     */
    async dailyCheckin(uid) {
        let sql = `INSERT INTO DailyCheckin(
                uid, continuousDays
            ) select ?, (
                select COALESCE(
                    (select continuousDays + 1 from DailyCheckin where uid = ? and DATE(checkinTime) = CURDATE() - INTERVAL 1 DAY),
                    1
                )
            ) where not exists (
                SELECT 1
                FROM DailyCheckin
                WHERE uid = ?
                AND DATE(checkinTime) = CURDATE()
            )`;
        let data = [ uid, uid, uid ];
        return mysql.sqlExec(sql, data);
    },
    async getDailyCheckinContinuousDays(uid) {
        let sql = `select continuousDays from DailyCheckin where uid = ? and DATE(checkinTime) = CURDATE()`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    },
    addTodo(uid, todo) {
        let sql = `INSERT INTO Todo (uid, title) VALUES (?, ?)`;
        let data = [ uid, todo.title ];
        return mysql.sqlExec(sql, data);
    },
    getAllTodos(uid) {
        let sql = `select
                id, title, status,
                DATE_FORMAT(createTime, '%Y-%m-%d %H:%i:%s') as createTime
            from Todo where uid = ?`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    },
    setTodoStatus(uid, todoId, status) {
        let sql = `update Todo set status = ? where id = ? and uid = ?`;
        let data = [ status, todoId, uid ];
        return mysql.sqlExec(sql, data);
    },
    deleteTodo(uid, todoId) {
        let sql = `delete from Todo where id = ? and uid = ?`;
        let data = [ todoId, uid ];
        return mysql.sqlExec(sql, data);
    }
};

module.exports = checkinDao;
